package com.maxd.respository.lucky;

import com.maxd.model.LuckyQueue;
import com.maxd.model.LuckyValueQueue;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface LuckyValueQueueRepository extends Repository<LuckyValueQueue, Long> {


    @Transactional
    @Modifying
    @Query(value = "update lucky_value_queue set lucky_queue_id=:luckyQueueId,create_time=:createTime,state=:state where user_id=:userId",nativeQuery = true)
    int updateLuckyValueQueue(@Param("userId") Long userId,@Param("luckyQueueId") Long luckyQueueId,@Param("state") Integer state,@Param("createTime") String createTime);

    @Modifying(clearAutomatically = true)
    @Query(value = "update lucky_value_queue set state=:state where user_id=:userId",nativeQuery = true)
    int updateLuckyValueQueue(@Param("state") Integer state,@Param("userId") Long userId);


    @Query(value = "select * from lucky_value_queue where user_id=:userId limit 1",nativeQuery = true)
    LuckyValueQueue selectLuckyValueQueueByUserId(@Param("userId") Long userId);

    @Query(value = "SELECT b.rank_num  FROM" +
            "(SELECT t.*,@rank_num \\:= @rank_num + 1 AS rank_num FROM " +
            "(SELECT @rank_num \\:= 0 ) r," +
            "(SELECT create_time AS createTime, user_id AS userId FROM lucky_value_queue WHERE lucky_queue_id =:luckyQueueId and state=1) AS t " +
            "ORDER BY t.createTime) AS b " +
            "WHERE b.userId = :userId",nativeQuery = true)
    Integer selectLuckQueueLocationByUserId(@Param("userId") Long userId ,@Param("luckyQueueId") Long luckyQueueId);

    @Query(value = "select count(*) from lucky_value_queue where lucky_queue_id =:luckyQueueId and state=1",nativeQuery = true)
    Integer selectLuckQueueCount(@Param("luckyQueueId") Long luckyQueueId);

    @Query(value = "SELECT count(*) FROM " +
            "lucky_value_queue v " +
            "left join lucky_queue q on q.id=v.lucky_queue_id " +
            "where v.user_id in (" +
            "select id from user_info where invitation=:invitation" +
            ") and q.sort>=:sort",nativeQuery = true)
    Integer selectUserLuckyQueueByInvitation(@Param("invitation") String invitation,@Param("sort") Integer sort);

    @Query(value = "SELECT b.rank_num AS rankNum,u.nick_name as nickName,u.image_url as imageUrl," +
            "(select lucky_value from help_lucky_value where user_id=u.id) as luckyValue FROM" +
            "(SELECT t.*,@rank_num \\:= @rank_num + 1 AS rank_num FROM " +
            "(SELECT @rank_num \\:= 0 ) r," +
            "(SELECT create_time AS createTime, user_id AS userId FROM lucky_value_queue WHERE lucky_queue_id =:luckyQueueId order by create_time limit 10) AS t " +
            "ORDER BY t.createTime ) AS b " +
            "left join user_info u on u.id=b.userId ",nativeQuery = true)
    List<Map<String,Object>> selectLuckQueueLocation(@Param("luckyQueueId") Long luckyQueueId);

    @Query(value = "SELECT b.rank_num AS rankNum,u.nick_name as nickName,u.image_url as imageUrl," +
            "(select count(*) from lucky_value_queue WHERE lucky_queue_id =:luckyQueueId ) as headCount, " +
            "(select lucky_value from help_lucky_value where user_id=u.id) as luckyValue, " +
            "(SELECT count(*) FROM user_info WHERE invitation =:invitation) as friendSum, " +
            "(select `name` from lucky_queue where id=b.luckyQueueId) as queueName FROM" +
            "(SELECT t.*,@rank_num \\:= @rank_num + 1 AS rank_num FROM " +
            "(SELECT @rank_num \\:= 0 ) r," +
            "(SELECT create_time AS createTime, user_id AS userId,lucky_queue_id as luckyQueueId FROM lucky_value_queue WHERE lucky_queue_id =:luckyQueueId ) AS t " +
            "ORDER BY t.createTime ) AS b " +
            "left join user_info u on u.id=b.userId " +
            "WHERE b.userId = :userId",nativeQuery = true)
    Map<String,Object> selectLuckQueueLocationByUserIds(@Param("invitation") String invitation,@Param("userId") Long userId ,@Param("luckyQueueId") Long luckyQueueId);


    @Query(value = "SELECT u.nick_name,u.image_url,u.id," +
            "coalesce(( SELECT lucky_value FROM help_lucky_value WHERE user_id = u.id ),0)  AS luckyValue,  " +
            "coalesce(q.name,'没有队列')  AS queueName  " +
            "FROM user_info u " +
            "LEFT JOIN  lucky_value_queue vq ON u.id = vq.user_id " +
            "LEFT JOIN lucky_queue q ON vq.lucky_queue_id = q.id " +
            "WHERE u.id IN ( SELECT id FROM user_info WHERE invitation =:invitation )"
            ,countQuery = "SELECT count(*) FROM user_info u " +
            "LEFT JOIN  lucky_value_queue vq ON u.id = vq.user_id " +
            "LEFT JOIN lucky_queue q ON vq.lucky_queue_id = q.id " +
            "WHERE u.id IN ( SELECT id FROM user_info WHERE invitation =:invitation )",
            nativeQuery = true)
    Page<Map<String,Object>> selectLuckyQueueByUser(Pageable pageable, @Param("invitation") String invitation);


    @Query(value = "select user_id from lucky_value_queue where lucky_queue_id=:luckyQueueId and state=1 order by create_time",nativeQuery = true)
    List<Long> selectLuckyQueueUserIdByLuckyQueueId(@Param("luckyQueueId")Long luckyQueueId);





}
